<!doctype html>
<html lang="zh_cn" itemscope itemtype="http://schema.org/Person">
<head>
            <meta charset="utf-8">
        <!-- Site Meta Data -->
        <title>[原创]MyBatis使用常见问题及特殊用法</title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="description" content="技术博客,涉及Java/PHP/Python/Javascript等,聊聊程序,聊聊生活,聊聊事实,聊聊育儿">
        <meta name="keywords" content="编码经验,技术分享,生活积累,实事评说">
        <meta name="author" content="布丁缘">

        <link rel="shortcut icon" href="">

        <link href='https://fonts.googleapis.com/css?family=Open+Sans:400,600,700' rel='stylesheet' type='text/css'>
        <!-- Style Meta Data -->
        <link rel="stylesheet" href="https://www.ddkiss.com/theme/css/style.css" type="text/css"/>
        <link rel="stylesheet" href="https://www.ddkiss.com/theme/css/pygments.css" type="text/css"/>

        <!-- Feed Meta Data -->
            <link href="https://www.ddkiss.com/feeds/all.atom.xml" type="application/atom+xml" rel="alternate"
                  title="一个程序员的简单生活 ATOM Feed"/>


    <meta name="keywords" content="">
    <link rel="stylesheet" href="//dn-coding-net-public-file.qbox.me/Coding-Comments/v0.1.0/default.css">
</head>

<body>
<!-- Sidebar -->
<aside>
    <center><h1><a href="/" style="color:#fff"><img id="avatar" src="/images/avatar.jpg"></a></h1></center>
        <p>一个程序员的简单生活</p>
    <br>
    <nav class="nav">
        <ul class="list-bare">
                <li><a class="nav__link" href="https://www.ddkiss.com/category/chang-yong-ji-qiao.html">常用技巧</a></li>
                <li><a class="nav__link" href="https://www.ddkiss.com/category/kai-fa-huan-jing.html">开发环境</a></li>
                <li><a class="nav__link" href="https://www.ddkiss.com/category/sheng-huo-dian-di.html">生活点滴</a></li>

                <li><a class="nav__link" href="/pages/books.html">书单</a></li>
                <li><a class="nav__link" href="/pages/movies.html">影单</a></li>
                <li><a class="nav__link" href="/pages/downloads.html">下载</a></li>
                <li><a class="nav__link" href="/pages/about.html">关于我</a></li>


        </ul>
    </nav>


    <form>
      <input type="text"  id="bdcsMain"/>
    </form>

</aside>

<!-- Content -->
<article>
  <main>
    <nav>
      <a href="/">首页</a>
      <a href="/archives.html">归档</a>
      <a href="/categories.html">分类</a>
      <a href="/tags.html">标签</a>
      <a href="/pages/about.html">关于我</a>
    </nav>
  </main>
    <section id="content">
        <article>
            <h2 class="post_title post_detail"><a href="https://www.ddkiss.com/archives/26.html" rel="bookmark"
                                                  title="Permalink to [原创]MyBatis使用常见问题及特殊用法">[原创]MyBatis使用常见问题及特殊用法</a></h2>

            <div class="post_list">
                <span>作者：</span><a href="https://www.ddkiss.com/author/bu-ding-yuan.html">布丁缘</a>
                <span class="post_category">分类：<a href="https://www.ddkiss.com/category/kai-fa-huan-jing.html" rel="bookmark"
                                               title="Permalink to 开发环境">开发环境</a></span>
                <span class="post_date">  时间：2017-09-25 17:56:00</span>

            </div>
            <div class="entry-content blog-post">
                <h2>问题</h2>
<h3>1. 对于list类似参数传值时，变量名需要注意下</h3>
<div class="highlight"><pre><span></span><span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;examstatusList != null &quot;</span><span class="nt">&gt;</span>
    and tbxxx.examstatus in
    <span class="nt">&lt;foreach</span> <span class="na">collection=</span><span class="s">&quot;examstatusList&quot;</span> <span class="na">item=</span><span class="s">&quot;examstatus&quot;</span> <span class="na">open=</span><span class="s">&quot;(&quot;</span> <span class="na">separator=</span><span class="s">&quot;,&quot;</span> <span class="na">close=</span><span class="s">&quot;)&quot;</span> <span class="nt">&gt;</span>
            #{examstatus}
    <span class="nt">&lt;/foreach&gt;</span>
<span class="nt">&lt;/if&gt;</span>

<span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;examstatus != null and examstatus != &#39;&#39;&quot;</span><span class="nt">&gt;</span>
　　and tbxxx.examstatus = #{examstatus}
<span class="nt">&lt;/if&gt;</span>
</pre></div>


<p>如果这样写。。即使参数传入 examstatusList = {"1","2","3"} 不传examstatus，mybatis会将examstatus赋值为"3"，也就是最后的结果是</p>
<p><code>and tbxxx.examstatus in ("01","02") and tbxxx.examstatus = "02"</code></p>
<p>解决办法：foreach中的item最好用 xxx_item 来命名！免得出现上述问题</p>
<h3>2. 属性名称如果转成大写是一致的话，返回结果会出问题。</h3>
<p><strong>描述</strong>：　CourseDto 中有两个属性 siteId 和 siteid
如果此时用　select siteid from course, 则返回的dto中，siteId 和 siteid　哪个有值是不确定的！！</p>
<p>原因：
反射获取类对象的方法时会存map。来自www.ddkiss.com</p>
<p>[siteId, setSiteId()], [siteid,setSiteid()]</p>
<p>然后获取属性时，全部变为大写，比如</p>
<p>[SITEID, siteid] 或者是 [SITEID, siteId]，因为代码如下</p>
<div class="highlight"><pre><span></span>    for (String propName : readablePropertyNames) {
      caseInsensitivePropertyMap.put(propName.toUpperCase(Locale.ENGLISH), propName);
    }

    for (String propName : writeablePropertyNames) {
      caseInsensitivePropertyMap.put(propName.toUpperCase(Locale.ENGLISH), propName);
    }
</pre></div>


<p>对map进行循环时顺序是没办法保证的！然后select siteid from course时，会把列转成大写，比如 SITEID，然后再找对应的属性，然后在获取这个属性的方法。</p>
<p>参考<a href="http://blog.csdn.net/isea533/article/details/46442067">这里</a></p>
<p>几个重要的类和方法</p>
<p>DefaultResultSetHander getRowValue</p>
<p>DefaultResultSetHander  applyAutomaticMappings</p>
<p>MetaClass buildProperty</p>
<p>Reflector findPropertyName</p>
<h3>3.启动时保存如下</h3>
<div class="highlight"><pre><span></span><span class="nt">Caused</span> <span class="nt">by</span><span class="o">:</span> <span class="nt">org</span><span class="p">.</span><span class="nc">springframework</span><span class="p">.</span><span class="nc">core</span><span class="p">.</span><span class="nc">NestedIOException</span><span class="o">:</span> <span class="nt">Failed</span> <span class="nt">to</span> <span class="nt">parse</span> <span class="nt">config</span> <span class="nt">resource</span><span class="o">:</span> <span class="nt">class</span> <span class="nt">path</span> <span class="nt">resource</span> <span class="cp">[</span><span class="nx">mybatis</span><span class="na">-config.xml</span><span class="cp">]</span><span class="o">;</span> <span class="nt">nested</span> <span class="nt">exception</span> <span class="nt">is</span> <span class="nt">org</span><span class="p">.</span><span class="nc">apache</span><span class="p">.</span><span class="nc">ibatis</span><span class="p">.</span><span class="nc">builder</span><span class="p">.</span><span class="nc">BuilderException</span><span class="o">:</span> <span class="nt">Error</span> <span class="nt">parsing</span> <span class="nt">SQL</span> <span class="nt">Mapper</span> <span class="nt">Configuration</span><span class="o">.</span> <span class="nt">Cause</span><span class="o">:</span> <span class="nt">org</span><span class="p">.</span><span class="nc">apache</span><span class="p">.</span><span class="nc">ibatis</span><span class="p">.</span><span class="nc">builder</span><span class="p">.</span><span class="nc">BuilderException</span><span class="o">:</span> <span class="nt">Error</span> <span class="nt">creating</span> <span class="nt">document</span> <span class="nt">instance</span><span class="o">.</span>  <span class="nt">Cause</span><span class="o">:</span> <span class="nt">org</span><span class="p">.</span><span class="nc">xml</span><span class="p">.</span><span class="nc">sax</span><span class="p">.</span><span class="nc">SAXParseException</span><span class="o">;</span> <span class="nt">lineNumber</span><span class="o">:</span> <span class="nt">165</span><span class="o">;</span> <span class="nt">columnNumber</span><span class="o">:</span> <span class="nt">2</span><span class="o">;</span> <span class="nt">The</span> <span class="nt">content</span> <span class="nt">of</span> <span class="nt">elements</span> <span class="nt">must</span> <span class="nt">consist</span> <span class="nt">of</span> <span class="nt">well-formed</span> <span class="nt">character</span> <span class="nt">data</span> <span class="nt">or</span> <span class="nt">markup</span><span class="o">.</span>
</pre></div>


<p>看了下 mybatis-config.xml　根本没有165行。。。然后debug，发现是读取mybatis-config.xml文件中制定的ordermapper.xml时出错。这个xml的报错提示也太……</p>
<h2>用法</h2>
<h3>1. 多个or条件怎么写呢？</h3>
<p>比如 select * from tbuser where site='110' and (username='abc' or mobile ='13972272727' or email='abc@163.com')</p>
<p>最后只能这么写了……</p>
<div class="highlight"><pre><span></span>select * from tbuser
 where
        (   1 != 1
            <span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;username != null and username != &#39;&#39;&quot;</span><span class="nt">&gt;</span>
                or t.username = #{username}
            <span class="nt">&lt;/if&gt;</span>

            <span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;email != null and email != &#39;&#39;&quot;</span><span class="nt">&gt;</span>
                or t.email = #{email}
            <span class="nt">&lt;/if&gt;</span>

            <span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;mobile != null and mobile != &#39;&#39;&quot;</span><span class="nt">&gt;</span>
                or t.mobile = #{mobile}
            <span class="nt">&lt;/if&gt;</span>
        )

        <span class="nt">&lt;if</span> <span class="na">test=</span><span class="s">&quot;platformSiteId != null and platformSiteId != &#39;&#39;&quot;</span><span class="nt">&gt;</span>
            and t.platform_site_id = #{platformSiteId}
        <span class="nt">&lt;/if&gt;</span>
</pre></div>


<h3>2. if语句判断是否为空</h3>
<p>参考<a href="http://blog.sina.com.cn/s/blog_667ac0360102vkpr.html">这里</a>, 有待验证</p>
<h3>3. 插入后返回key</h3>
<p>用keyProperty属性，比如</p>
<div class="highlight"><pre><span></span> &lt;insert id=&quot;insert&quot; parameterType=&quot;Contact&quot;　useGeneratedKeys=&quot;true&quot;  keyProperty=&quot;id&quot;&gt;
</pre></div>


<p>然后在java里通过返回值获取</p>
<div class="highlight"><pre><span></span>insert(user);
user.getId();
</pre></div>


<p>参考<a href="http://www.cnblogs.com/java233/p/6072821.html">这里</a>，useGeneratedKeys 取值范围true|false 默认值是：false。 含义：设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中</p>
<h2>和Spring的集成</h2>
<p>参考<a href="http://blog.csdn.net/z69183787/article/details/51566522">这里</a> 默认的mapper扫描没有问题，但是<a href="http://blog.csdn.net/isea533/article/details/45640319">typeAliasesPackage不支持ant风格路径</a>，必须把默认的SqlSessionFactoryBean重写方法。</p>
<h2>批量更新</h2>
<p>参考 https://my.oschina.net/zhuguowei/blog/411853，20170718又一次遇到这个问题了。如下：</p>
<div class="highlight"><pre><span></span>修改mysql连接配置，　jdbc:mysql://127.0.0.1:3307/dcrm?allowMultiQueries=true
</pre></div>
            </div>
            <div class="post_list">
              <div><span>Tags : </span>
                          <span><a href="https://www.ddkiss.com/tag/mybatis.html">#mybatis, </a></span>
              </div>
            </div>
        </article>
        <div id="container"></div>
        <script type="text/javascript" src="//dn-coding-net-public-file.qbox.me/Coding-Comments/v0.1.0/gitment.min.js"></script>
        <script>
            var gitment = new Gitment({
              owner: 'whusl',
              repo: 'BlogComments',
              oauth: {
                client_id: '621866266817529fba46681653017809',
                client_secret: '14188411740b12ae52159cee9b586bf85cd54125',
              },
            })
            document.getElementById('container').appendChild(gitment.render())
          </script>
    </section>
</article>

<!-- Footer -->
    <footer>
        <p> &copy;2017-2020&nbsp;<a href="http://www.miitbeian.gov.cn/" target="_blank">鄂ICP备17020200号</a>
          Blog powered by <a href="http://getpelican.com/">Pelican</a>
        </p>
    </footer>

    <!-- Analytics -->
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?88c55edaf311dbacac56a16316b04c8b";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>

<script type="text/javascript">(function(){document.write(unescape('%3Cdiv id="bdcs"%3E%3C/div%3E'));var bdcs = document.createElement('script');bdcs.type = 'text/javascript';bdcs.async = true;bdcs.src = 'http://znsv.baidu.com/customer_search/api/js?sid=14490611060029767912' + '&plate_url=' + encodeURIComponent(window.location.href) + '&t=' + Math.ceil(new Date()/3600000);var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(bdcs, s);})();</script>

</body>
</html>